from PyQt5.QtCore import  QByteArray
from fastapi import APIRouter
from pydantic import BaseModel
from func import imgs,data
from db.db_work import MysqlLite
import base64
from typing import Union


router = APIRouter()

class Shop(BaseModel):
    shop_name: str
    seller_name: str
    price: float
    brand:  str
    category: str
    description: str
    avatar: str
    num: int

class DeleteShop(BaseModel):
    shop_name: str
    seller_name: str


class Updata_Shop(BaseModel):
    shop_name: str
    seller_name: str
    quantity: int

class By_man(BaseModel):
    shop_name: str
    seller_name: str
    user_name: str
    quantity :int
    data : str

class Order_Shop(BaseModel):
    shop_id: int
    user_id: int
    quantity : int
    date: str

class Comment(BaseModel):
    order_id: int
    star: int
    content: str
@router.post('/add_shop')
def add_shop(shop: Shop):
    shop_name = shop.shop_name
    seller_name = shop.seller_name
    shop_price = shop.price
    shop_brand = shop.brand
    shop_category = shop.category
    shop_description = shop.description
    avatar = base64.b64decode(shop.avatar)
    shop_avatar = QByteArray(imgs.resize_(avatar,200,200))
    num=shop.num
    mysql = MysqlLite()
    mysql.operation_sql("create table if not exists shops (ID integer primary Key AUTOINCREMENT,shop_name text,sellername text,price float,brand text,category text,description text,avatar BLOB,num int,exit bool,user_id INTEGER,FOREIGN KEY (user_id) REFERENCES user_info(ID) ON DELETE CASCADE)")
    shop_exit=mysql.exit_("select * from shops where shop_name=? and sellername=?",(shop_name,seller_name))
    if shop_exit:
        return {
            'code': 400,
            'msg': '该商品已添加',
        }
    else:
        user_id=mysql.select_sql("select ID from user_info where username=?",(seller_name,))[0][0]
        mysql.operation_sql("insert into shops(shop_name,sellername,price,brand,category,description,avatar,num,exit,user_id)values(?,?,?,?,?,?,?,?,?,?) ",(shop_name,seller_name,shop_price,shop_brand,shop_category,shop_description,shop_avatar,num,True,user_id))
        return {
            'code': 200,
            'msg': '商品添加成功',
        }

@router.delete('/delete_shop')
def delete_shop(shop: DeleteShop):
    shop_name = shop.shop_name
    seller_name = shop.seller_name
    mysql = MysqlLite()
    mysql.operation_sql("create table if not exists shops (ID integer primary Key AUTOINCREMENT,shop_name text,sellername text,price float,brand text,category text,description text,avatar BLOB,num int,exit bool,user_id INTEGER,FOREIGN KEY (user_id) REFERENCES user_info(ID) ON DELETE CASCADE)")
    result = mysql.select_sql(("select ID from shops where shop_name=? and sellername=?"), (shop_name, seller_name))[0][0]
    if result:
        mysql.operation_sql("update shops set exit=3 where shop_name=? and sellername=?", (shop_name, seller_name))
        return{
            'code' : 200,
            'msg'  :'成功下架'

        }
    else:
        return{
            'code' : 400,
            'msg'  : '不存在该商品'
        }


#展示商品，mode为1时为视角是买家，他看不到库存为0，或卖家被拉黑的商品 2可查看库存为0的商品，3卖家被拉黑的商品 4为下架商品
@router.get('/get_shops')
def get_all_shops(mode: int,user_name: Union[str,None]=None,shop_name : Union[str, None] = None, seller_name : Union[str, None] = None,brand : Union[str, None] = None,category : Union[str, None] = None,description : Union[str, None] = None,avatar : Union[str, None] = None):
    lis=[]
    sql="select * from shops"
    count=0
    if shop_name:
        sql=sql+' where shop_name=?'
        count+=1
        lis.append(shop_name)
    if seller_name:
        if count==0:
            sql=sql+' where sellername=?'
            count+=1
        else:
            sql=sql+' and sellername=?'
        lis.append(seller_name)
    if brand:
        if count==0:
            sql=sql+' where brand=?'
            count+=1
        else:
            sql=sql+' and brand=?'
        lis.append(brand)
    if category:
        if count==0:
            sql=sql+' where category=?'
            count+=1
        else:
            sql=sql+' and category=?'
        lis.append(category)
    if user_name:
        if count==0:
            sql=sql+' where sellername!=?'
            count += 1
        else:
            sql=sql+' and sellername!=?'
        lis.append(user_name)
    tup=tuple(lis)
    mysql = MysqlLite()
    mysql.operation_sql("create table if not exists shops (ID integer primary Key AUTOINCREMENT,shop_name text,sellername text,price float,brand text,category text,description text,avatar BLOB,num int,exit bool,user_id INTEGER,FOREIGN KEY (user_id) REFERENCES user_info(ID) ON DELETE CASCADE)")
    lis1 = mysql.select_sql("PRAGMA table_info(shops);")
    if mode==1:
        if count == 0:
            sql = sql + ' where exit=1'
        else:
            sql = sql + ' and exit=1'
    if mode==2:
        if count == 0:
            sql = sql + ' where exit!=2 and exit!=3'
        else:
            sql = sql + ' and exit!=2 and exit!=3'
    if mode==3:
        if count == 0:
            sql = sql + ' where exit=2'
        else:
            sql = sql + ' and exit=2'
    if mode==4:
        if count == 0:
            sql = sql + ' where exit=3'
        else:
            sql = sql + ' and exit=3'
    lis2=mysql.select_sql(sql,tup)
    result=data.get_data(lis1,lis2)
    return {
        'code': 200,
        'data': result,
    }

#用户购买商品后，修改商品库存
@router.put('/buy_shop')
def buy_shop(shop: Updata_Shop):
    quantity=shop.quantity
    shop_name = shop.shop_name
    seller_name = shop.seller_name
    mysql = MysqlLite()
    mysql.operation_sql("create table if not exists shops (ID integer primary Key AUTOINCREMENT,shop_name text,sellername text,price float,brand text,category text,description text,avatar BLOB,num int,exit,user_id INTEGER,FOREIGN KEY (user_id) REFERENCES user_info(ID) ON DELETE CASCADE)")
    shop_exit = mysql.select_sql("select num from shops where shop_name=? and sellername=? and num>=?", (shop_name, seller_name,quantity))
    if shop_exit:
        mysql.operation_sql("update shops set num=num-? where shop_name=? and sellername=?", (quantity,shop_name, seller_name))
        num=shop_exit[0][0]
        if num == quantity:
            mysql.operation_sql("update shops set exit = 0 where shop_name=? and sellername=?", (shop_name, seller_name))
        return{
            'code' : 200,
            'msg':'购买成功'
        }
    else:
        return{
            'code': 400,
            'msg' :'商品以经没有库存',
        }

@router.put('/update_shop')
def update_shop(shop: Shop):
    shop_name = shop.shop_name
    seller_name = shop.seller_name
    brand = shop.brand
    price = shop.price
    category = shop.category
    description = shop.description
    avatar = base64.b64decode(shop.avatar)
    shop_avatar = QByteArray(imgs.resize_(avatar, 200, 200))
    num=shop.num
    mysql=MysqlLite()
    if num > 0:
        exit=True
    else:
        exit=False
    mysql.operation_sql('update shops set shop_name= ?,brand=?,price=?,category=?,description=?,avatar=?,num=?,exit=? where shop_name=? and sellername=?',(shop_name, brand, price, category, description, shop_avatar, num,exit,shop_name,seller_name))

#当用户买商品，记录存入数据库
@router.post('/history_buy')
def history_buy(by_man:By_man):
    user_name = by_man.user_name
    shop_name = by_man.shop_name
    seller_name = by_man.seller_name
    quantity = by_man.quantity
    data=by_man.data
    mysql = MysqlLite()
    mysql.operation_sql("""CREATE TABLE IF NOT EXISTS history_buy (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    dated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    quantity INTEGER,
    sum_ int,
    shop_id INTEGER,
    user_id INTEGER,
    comment int,
    comments text,
    FOREIGN KEY (shop_id) REFERENCES shops(ID) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES user_info(ID) ON DELETE CASCADE
);""")
    user_id=mysql.select_sql("select ID from user_info where username=?", (user_name,))[0][0]
    shop_id_and_price=mysql.select_sql("select ID,price from shops where shop_name=? and sellername=?", (shop_name,seller_name))[0]
    mysql.operation_sql("insert into history_buy(dated_at,quantity,sum_,shop_id,user_id,comment,comments)values(?,?,?,?,?,0,null) ",(data,quantity,shop_id_and_price[1]*quantity,shop_id_and_price[0],user_id))
    return {
        'code': 200,
        'msg':'success'
    }
#用户发起订单请求
@router.post('/post_orders')
def post_orders(order: Order_Shop):
    user_id=order.user_id
    shop_id=order.shop_id
    date=order.date
    quantity=order.quantity
    mysql = MysqlLite()
    mysql.operation_sql("""CREATE TABLE IF NOT EXISTS orders (
        ID INTEGER PRIMARY KEY AUTOINCREMENT,
        date_ TEXT DEFAULT CURRENT_TIMESTAMP,
        quantity INTEGER,
        sum_ int,
        shop_id INTEGER,
        user_id INTEGER,
        FOREIGN KEY (shop_id) REFERENCES shops(ID) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES user_info(ID) ON DELETE CASCADE
    );""")
    exit_ = mysql.exit_('select * from shops where ID=? and num >0', (shop_id,))
    if exit_:
        mysql.operation_sql('insert into orders(date_,quantity,shop_id,user_id) values(?,?,?,?)',(date,quantity,shop_id,user_id))
        return {
            'code': 200,
            'msg':'订单添加成功'
        }
    else:
        return {
            'code': 400,
            'msg':'库存已售完'
        }
#获取用户所有订单
@router.get('/get_user_orders')
def get_orders(user_id:int):
    finish_order=data.get_finish_orders(user_id=user_id)
    no_finish_order=data.get_no_finish_orders(user_id=user_id)
    return {
        'code': 200,
        'msg': {
            'finish_order': finish_order,
            'no_finish_order': no_finish_order
        }
    }
#获取商家所有订单
@router.get('/get_seller_orders')
def get_seller_orders(seller_id:int):

    finish_order=data.get_finish_orders(seller_id=seller_id)
    no_finish_order=data.get_no_finish_orders(seller_id=seller_id)
    return {
        'code': 200,
        'msg':{
            'finish_order': finish_order,
            'no_finish_order': no_finish_order
        }
    }
#获取订单
@router.get('/get_orders')
def get_orders(user_id: Union[int,None]=None,seller_id: Union[int,None]=None):
    if seller_id:
        finish_order=data.get_finish_orders(seller_id=seller_id)
        no_finish_order=data.get_no_finish_orders(seller_id=seller_id)
    else :
        finish_order = data.get_finish_orders(user_id=user_id)
        no_finish_order = data.get_no_finish_orders(user_id=user_id)
    return {
        'code': 200,
        'msg': {
            'finish_order': finish_order,
            'no_finish_order': no_finish_order
        }
    }
#删除订单
@router.delete('/delete_orders')
def delete_orders(orders_id:int):
    mysql = MysqlLite()
    mysql.operation_sql('delete from orders where ID=?', (orders_id,))
    return{
        'code': 200,
        'msg' : 'success'
    }
#用户评价
@router.put('/set_comment')
def set_comment(comment : Comment):
    order_id = comment.order_id
    star=comment.star
    content=comment.content
    mysql = MysqlLite()
    mysql.operation_sql("update history_buy set comment=?,comments=? where ID=?",(star,content,order_id,))
    return {
        'code': 200,
        'msg' : 'success'
    }
#查找莫商品的所有用户评价
@router.get('/get_comment')
def get_comment(shop_id:int):
    mysql = MysqlLite()
    data=[]
    dt=mysql.select_sql("select user_id,dated_at,comment,comments from history_buy where shop_id=? and comment!=0", (shop_id,))
    if len(dt)==0:
        return {
            'code': 400,
            'msg' :'该商品没有任何评价'
        }
    else:
        for i in dt:
            result={}
            user_name=mysql.select_sql(f"select username from user_info where ID=?", (i[0],))
            result['date']=i[1]
            result['username']=user_name[0][0]
            result['comment']=i[2]
            result['comments']=i[3]
            data.append(result)
        data.sort(key=lambda x:x['date'])
        return{
            'code': 200,
            'msg': data
        }
#用户模糊查询商品
@router.get('/fuzzy_select_shop')
def fuzzy_select_shop(information : str, user_id:int):
    mysql = MysqlLite()
    lis1 = mysql.select_sql("PRAGMA table_info(shops);")
    lis2=mysql.select_sql("SELECT * FROM shops WHERE (shop_name LIKE ? or brand LIKE ?) and user_id !=? and exit=1",('%'+information+'%','%'+information+'%',user_id,))
    if lis2:
        result = data.get_data(lis1, lis2)
        return{
            'code': 200,
            'msg': 'success',
            'data':result
        }
    else:
        return{
            'code': 400,
            'msg':'未查到任何商品'
        }


@router.delete('/delete')
def delete():
    mysql = MysqlLite()
    mysql.operation_sql('''UPDATE history_buy 
SET sum_ = (
    SELECT price * history_buy.quantity 
    FROM shops 
    WHERE shops.ID = history_buy.shop_id
);''')
@router.put('/exit3_1')
def exit1():
    mysql = MysqlLite()
    mysql.operation_sql('ALTER TABLE history_buy ADD COLUMN comments tetx DEFAULT null;')





    """{
  "shop_name": "小米15",
  "seller_name": "张三",
  "user_name": "孙金淦",
  "quantity": 11,
  "data": "2025.10.10"
}"""